import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 以下测试结果全部在个人机器上得出，机器配置如下
 * 处理器	Intel(R) Core(TM) i5-4200H CPU @ 2.80GHz   2.79 GHz
 * 机带 RAM	8.00 GB (7.89 GB 可用)
 * 设备 ID	E26E54C9-DD07-43CA-9094-51C2CF4A1564
 * 产品 ID	00331-20300-00000-AA052
 * 系统类型	64 位操作系统, 基于 x64 的处理器
 * 版本	Windows 10 专业版
 * 版本号	21H1
 * 操作系统内部版本	19043.1288
 */

public class MysqlInsertTest {

    private static final Connection conn = MysqlConn.getConnection();

    private final int COUNT = 1000000;

    /**
     * 一、普通方式
     * 时间:3186605ms 约为53分钟
     */
    public void test1() throws SQLException {
        PreparedStatement stmt = null;
        long t1 = System.currentTimeMillis();
        stmt = conn.prepareStatement("insert into `order_1`(order_no) values(?)");
        for (int i = 0; i < COUNT; i++) {
            stmt.setString(1, i+ String.valueOf(System.currentTimeMillis()));
            stmt.execute();
        }
        //conn.close();
        long t2 = System.currentTimeMillis();
        System.out.println((t2 - t1));
    }

    /**
     * 二、事务提交 :
     * 时间:87207ms 87秒
     */
    public void test2() throws SQLException {
        PreparedStatement stmt = null;
        long t1 = System.currentTimeMillis();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement("insert into `order_2`(order_no) values(?)");
        for (int i = 0; i < COUNT; i++) {
            stmt.setString(1, i+ String.valueOf(System.currentTimeMillis()));
            stmt.execute();
        }
        conn.commit();
        //conn.close();
        long t2 = System.currentTimeMillis();
        System.out.println((t2 - t1));
    }

    /**
     * 事务+批处理并且分批执行
     * 时间:105372 ms  105秒
     */

    public void test3() throws SQLException {
        PreparedStatement stmt = null;
        long t1 = System.currentTimeMillis();
        stmt = conn.prepareStatement("insert into `order_4`(order_no) values(?)");
        int size = COUNT;
        for (int i = 0; i < size; i++) {
            stmt.setString(1, i+ String.valueOf(System.currentTimeMillis()));
            stmt.addBatch();
            if (i % 10000 == 0 || i == size - 1) { //1万次一条，或者最后一次进行提交。
                stmt.executeBatch();
                stmt.clearBatch(); /**清除缓存*/
                System.out.println("执行到第" + i / 10000 + "外循环");
            }
        }

        conn.close();
        long t2 = System.currentTimeMillis();
        System.out.println((t2 - t1));
    }
}
